//
//  ChatSql.m
//  HiCar
//
//  Created by huachun on 2019/3/22.
//  Copyright © 2019 丁一. All rights reserved.
//

#import "ChatSql.h"
#import "ChatModel.h"
#import <sqlite3.h>

@interface ChatSql (){
    sqlite3 *db;
}

@end

@implementation ChatSql

-(instancetype)init{
    if (self=[super init]) {
        [self OpenHere];
    }
    return self;
}

-(void)OpenHere{
    NSString *path=[self getdbpath];
    //将oc的字符串转换成c语言的字符串
    const char *filename=[path UTF8String];
    //打开数据库(如果数据库不存在的话,直接创建并打开)
    if (sqlite3_open(filename, &db)==SQLITE_OK) {
        NSLog(@"数据库创建成功");
        NSString *sql=@"CREATE TABLE IF NOT EXISTS  lettertable(letter_id integer PRIMARY KEY AUTOINCREMENT,letter_user_id text NOT NULL, letter_user_name text NOT NULL,letter_user_logo text NOT NULL, letter_title text NOT NULL,letter_date text NOT NULL,letter_du text NOT NULL,letter_type text NOT NULL,login_id text NOT NULL,send_type text NOT NULL,sex text NOT NULL,briday text NOT NULL)";
        //1.数据库对象,sql语句,回调函数,空指针,错误的信息
        if (sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL)==SQLITE_OK) {
            NSLog(@"表创建成功");
        }
        else
        {
            NSLog(@"表创建失败");
        }
    }
    else
    {
        NSLog(@"数据库创建失败");
        sqlite3_close(db);
    }
    
    
    NSString *sqlbg;
    if ([UserModel info].userid) {
        sqlbg=[NSString stringWithFormat:@"alter table lettertable add login_id TEXT NOT NULL default '%@'",[UserModel info].userid];
        if (sqlite3_exec(db, sqlbg.UTF8String, NULL, NULL, NULL)==SQLITE_OK) {
            NSLog(@"ok");
        }else{
            NSLog(@"no");
        }
    }
}

- (void)uadatedata:(NSString *)user_id {
    
    NSString *sqlstring = [NSString stringWithFormat:@"UPDATE lettertable SET letter_du='%@' WHERE letter_user_id='%@' and login_id='%@'",@"1",user_id,[UserModel info].userid];
    const char *sql=sqlstring.UTF8String;
    sqlite3_exec(db, sql, NULL, NULL, NULL);
}

- (void)uadatedatatype:(ChatModel *)model{
    NSString *sqlstring = [NSString stringWithFormat:@"UPDATE lettertable SET send_type='%@' WHERE letter_user_id='%@' and letter_date='%@'",model.Send_type,model.User_id,model.Chat_date];
    const char *sql=sqlstring.UTF8String;
    sqlite3_exec(db, sql, NULL, NULL, NULL);
}

- (void)uadatedatadu:(ChatModel *)model{
    NSString *sqlstring = [NSString stringWithFormat:@"UPDATE lettertable SET letter_du='%@' WHERE letter_user_id='%@' and letter_date='%@'",model.IsRead,model.User_id,model.Chat_date];
    const char *sql=sqlstring.UTF8String;
    sqlite3_exec(db, sql, NULL, NULL, NULL);
}

- (void)Insertdata:(ChatModel *)model {
    
    NSString *sql=[NSString stringWithFormat:@"INSERT INTO lettertable(letter_user_id,letter_user_name,letter_user_logo,letter_title,letter_date,letter_du,letter_type,login_id,send_type,sex,briday) VALUES('%@','%@','%@','%@','%@','%@','%@','%@','%@','%@','%@')",model.User_id,model.User_name,model.User_logo,model.Chat_info,model.Chat_date,model.IsRead,model.Chat_type,[UserModel info].userid,model.Send_type,model.sex,model.briday];
    if (sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL)==SQLITE_OK) {
        NSLog(@"ok");
    }else{
        NSLog(@"no");
    }
}

- (void)deletedata:(ChatModel *)model {
    
    NSString *sqlstring = [NSString stringWithFormat:@"DELETE FROM lettertable WHERE letter_user_id='%@' and login_id='%@'",model.User_id,[UserModel info].userid];
    
    const char *sql=sqlstring.UTF8String;
    sqlite3_exec(db, sql, NULL, NULL, NULL);
}

-(void)deletedataall{
    NSString *sqlstring = [NSString stringWithFormat:@"DELETE FROM lettertable WHERE 1 = 1"];
    
    const char *sql=sqlstring.UTF8String;
    sqlite3_exec(db, sql, NULL, NULL, NULL);
}

- (NSMutableArray *)selectdata{
    
    //const char *sql="SELECT *FROM lettertable order by letter_date desc";
    NSString *sql_string=[NSString stringWithFormat:@"select * from lettertable where letter_id in (select max(letter_id) from lettertable where login_id ='%@' group by letter_user_id) order by letter_id desc",[UserModel info].userid];
    const char *sql=sql_string.UTF8String;
    
    
    //缓冲
    sqlite3_stmt *stmt=NULL;
    
    NSMutableArray *linshiArray=[NSMutableArray new];
    
    if (sqlite3_prepare(db, sql, -1, &stmt, NULL)==SQLITE_OK) {
        while (sqlite3_step(stmt)==SQLITE_ROW) {
            
            int news_id=sqlite3_column_int(stmt, 0);
            const unsigned char *news_user_id=sqlite3_column_text(stmt, 1);
            const unsigned char *news_user_name=sqlite3_column_text(stmt, 2);
            const unsigned char *news_user_logo=sqlite3_column_text(stmt, 3);
            const unsigned char *news_ping_title=sqlite3_column_text(stmt, 4);
            const unsigned char *news_ping_date=sqlite3_column_text(stmt, 5);
            const unsigned char *news_ping_du=sqlite3_column_text(stmt, 6);
            const unsigned char *news_ping_type=sqlite3_column_text(stmt, 7);
            const unsigned char *news_send_type=sqlite3_column_text(stmt, 9);
            const unsigned char *news_send_sex=sqlite3_column_text(stmt, 10);
            const unsigned char *news_send_briday=sqlite3_column_text(stmt, 11);
            
            ChatModel *model = [ChatModel new];
            model.Chat_id=[NSString stringWithFormat:@"%i",news_id];
            model.User_id=[NSString stringWithFormat:@"%s",news_user_id];
            model.User_name=[NSString stringWithUTF8String:news_user_name];
            model.User_logo=[NSString stringWithFormat:@"%s",news_user_logo];
            model.Chat_info=[NSString stringWithUTF8String:news_ping_title];
            model.Chat_date=[NSString stringWithFormat:@"%s",news_ping_date];
            model.IsRead=[NSString stringWithFormat:@"%s",news_ping_du];
            model.Chat_type=[NSString stringWithFormat:@"%s",news_ping_type];
            model.Send_type=[NSString stringWithFormat:@"%s",news_send_type];
            model.sex=[NSString stringWithFormat:@"%s",news_send_sex];
            model.briday=[NSString stringWithFormat:@"%s",news_send_briday];
            
            [linshiArray addObject:model];
        }
    }
    
    NSLog(@"%@",linshiArray);
    return linshiArray;
    
    
}


- (NSMutableArray *)selectdatanews:(NSString *)user_id{
    
    NSString *sqlstring = [NSString stringWithFormat:@"SELECT *FROM lettertable where letter_user_id='%@' and login_id ='%@' order by letter_id ASC",user_id,[UserModel info].userid];
    
    const char *sql=sqlstring.UTF8String;
    
    //缓冲
    sqlite3_stmt *stmt=NULL;
    
    NSMutableArray *linshiArray=[NSMutableArray new];
    
    if (sqlite3_prepare(db, sql, -1, &stmt, NULL)==SQLITE_OK) {
        while (sqlite3_step(stmt)==SQLITE_ROW) {
            
            int news_id=sqlite3_column_int(stmt, 0);
            const unsigned char *news_user_id=sqlite3_column_text(stmt, 1);
            const unsigned char *news_user_name=sqlite3_column_text(stmt, 2);
            const unsigned char *news_user_logo=sqlite3_column_text(stmt, 3);
            const unsigned char *news_ping_title=sqlite3_column_text(stmt, 4);
            const unsigned char *news_ping_date=sqlite3_column_text(stmt, 5);
            const unsigned char *news_ping_du=sqlite3_column_text(stmt, 6);
            const unsigned char *news_ping_type=sqlite3_column_text(stmt, 7);
            const unsigned char *news_send_type=sqlite3_column_text(stmt, 9);
            const unsigned char *news_send_sex=sqlite3_column_text(stmt, 10);
            const unsigned char *news_send_briday=sqlite3_column_text(stmt, 11);
            
            ChatModel *model = [ChatModel new];
            model.Chat_id=[NSString stringWithFormat:@"%i",news_id];
            model.User_id=[NSString stringWithFormat:@"%s",news_user_id];
            model.User_name=[NSString stringWithUTF8String:news_user_name];
            model.User_logo=[NSString stringWithFormat:@"%s",news_user_logo];
            model.Chat_info=[NSString stringWithUTF8String:news_ping_title];
            model.Chat_date=[NSString stringWithFormat:@"%s",news_ping_date];
            model.IsRead=[NSString stringWithFormat:@"%s",news_ping_du];
            model.Chat_type=[NSString stringWithFormat:@"%s",news_ping_type];
            model.Send_type=[NSString stringWithFormat:@"%s",news_send_type];
            model.sex=[NSString stringWithFormat:@"%s",news_send_sex];
            model.briday=[NSString stringWithFormat:@"%s",news_send_briday];
            
            [linshiArray addObject:model];
        }
    }
    return linshiArray;
}


- (NSMutableArray *)selectdatanewstixing:(NSString *)user_id{
    NSString *sqlstring = [NSString stringWithFormat:@"SELECT *FROM lettertable where letter_user_id='%@' and letter_du = '0' and login_id ='%@' order by letter_id ASC",user_id,[UserModel info].userid];
    
    const char *sql=sqlstring.UTF8String;
    
    //缓冲
    sqlite3_stmt *stmt=NULL;
    
    NSMutableArray *linshiArray=[NSMutableArray new];
    
    if (sqlite3_prepare(db, sql, -1, &stmt, NULL)==SQLITE_OK) {
        while (sqlite3_step(stmt)==SQLITE_ROW) {
            
            int news_id=sqlite3_column_int(stmt, 0);
            const unsigned char *news_user_id=sqlite3_column_text(stmt, 1);
            const unsigned char *news_user_name=sqlite3_column_text(stmt, 2);
            const unsigned char *news_user_logo=sqlite3_column_text(stmt, 3);
            const unsigned char *news_ping_title=sqlite3_column_text(stmt, 4);
            const unsigned char *news_ping_date=sqlite3_column_text(stmt, 5);
            const unsigned char *news_ping_du=sqlite3_column_text(stmt, 6);
            const unsigned char *news_ping_type=sqlite3_column_text(stmt, 7);
            const unsigned char *news_send_type=sqlite3_column_text(stmt, 8);
            const unsigned char *news_send_sex=sqlite3_column_text(stmt, 10);
            const unsigned char *news_send_briday=sqlite3_column_text(stmt, 11);
            
            ChatModel *model = [ChatModel new];
            model.Chat_id=[NSString stringWithFormat:@"%i",news_id];
            model.User_id=[NSString stringWithFormat:@"%s",news_user_id];
            model.User_name=[NSString stringWithUTF8String:news_user_name];
            model.User_logo=[NSString stringWithFormat:@"%s",news_user_logo];
            model.Chat_info=[NSString stringWithUTF8String:news_ping_title];
            model.Chat_date=[NSString stringWithFormat:@"%s",news_ping_date];
            model.IsRead=[NSString stringWithFormat:@"%s",news_ping_du];
            model.Chat_type=[NSString stringWithFormat:@"%s",news_ping_type];
            model.Send_type=[NSString stringWithFormat:@"%s",news_send_type];
            model.sex=[NSString stringWithFormat:@"%s",news_send_sex];
            model.briday=[NSString stringWithFormat:@"%s",news_send_briday];
            
            
            [linshiArray addObject:model];
        }
    }
    return linshiArray;
}


- (NSMutableArray *)selectdatanews{
    NSString *sqlstring = [NSString stringWithFormat:@"SELECT *FROM lettertable where letter_du=0 and login_id ='%@' order by letter_date desc",[UserModel info].userid];
    const char *sql=sqlstring.UTF8String;
    //缓冲
    sqlite3_stmt *stmt=NULL;
    
    NSMutableArray *linshiArray=[NSMutableArray new];
    
    if (sqlite3_prepare(db, sql, -1, &stmt, NULL)==SQLITE_OK) {
        while (sqlite3_step(stmt)==SQLITE_ROW) {
            
            int news_id=sqlite3_column_int(stmt, 0);
            const unsigned char *news_user_id=sqlite3_column_text(stmt, 1);
            const unsigned char *news_user_name=sqlite3_column_text(stmt, 2);
            const unsigned char *news_user_logo=sqlite3_column_text(stmt, 3);
            const unsigned char *news_ping_title=sqlite3_column_text(stmt, 4);
            const unsigned char *news_ping_date=sqlite3_column_text(stmt, 5);
            const unsigned char *news_ping_du=sqlite3_column_text(stmt, 6);
            const unsigned char *news_ping_type=sqlite3_column_text(stmt, 7);
            const unsigned char *news_send_type=sqlite3_column_text(stmt, 8);
            const unsigned char *news_send_sex=sqlite3_column_text(stmt, 10);
            const unsigned char *news_send_briday=sqlite3_column_text(stmt, 11);
            
            ChatModel *model = [ChatModel new];
            model.Chat_id=[NSString stringWithFormat:@"%i",news_id];
            model.User_id=[NSString stringWithFormat:@"%s",news_user_id];
            model.User_name=[NSString stringWithUTF8String:news_user_name];
            model.User_logo=[NSString stringWithFormat:@"%s",news_user_logo];
            model.Chat_info=[NSString stringWithUTF8String:news_ping_title];
            model.Chat_date=[NSString stringWithFormat:@"%s",news_ping_date];
            model.IsRead=[NSString stringWithFormat:@"%s",news_ping_du];
            model.Chat_type=[NSString stringWithFormat:@"%s",news_ping_type];
            model.Send_type=[NSString stringWithFormat:@"%s",news_send_type];
            model.sex=[NSString stringWithFormat:@"%s",news_send_sex];
            model.briday=[NSString stringWithFormat:@"%s",news_send_briday];
            
            [linshiArray addObject:model];
        }
    }
    return linshiArray;
}

-(NSString*)getdbpath{
    NSString *docsDir = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)objectAtIndex:0];
    NSString *databasePath = [[NSString alloc]initWithString:[docsDir stringByAppendingPathComponent:@"database.sqlite"] ];
    return databasePath;
}

@end
